from selenium import webdriver
from selenium.webdriver.chrome.options import Options
import time,os
from selenium.webdriver import ActionChains
from selenium.webdriver.support.ui import WebDriverWait

def foo():
    options = Options()
    options.add_argument('--disable-infobars')
    options.add_argument('--incognito')
    # options.add_argument('--headless')
    bro = webdriver.Chrome(executable_path='C:\drf2\drf2\chromedriver.exe', chrome_options=options)
    bro.maximize_window()
    bro.get('')
    # 定义隐式等待
    bro.implicitly_wait(10)
    # 先删除'C:/Users/0717/Downloads/ 下的文件
    if os.path.exists((r'C:/Users/0717/Downloads/设备表.xlsx')):
        for i in range(1, 8):
            os.remove(r'C:/Users/0717/Downloads/设备表 (%s).xlsx' % i)
        os.remove(r'C:/Users/0717/Downloads/设备表.xlsx')
    u = bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div/div[3]/div[1]/div[1]/input')
    u.send_keys('')
    # sleep(1)
    p = bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div/div[3]/div[1]/div[2]/input')
    p.send_keys('')
    # sleep(1)

    # 点击登录按钮
    bro.find_element_by_class_name('el-button').click()

    # 选择项目
    bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div/div[3]/form/div[1]/div/div/div/input').click()
    btn = bro.find_element_by_xpath('/html/body/div[2]/div[1]/div[1]/ul/li[11]/span')
    # 移动元素element对象的顶端与当前窗口的顶部对齐
    # https://blog.csdn.net/lilongsy/article/details/76142497 Selenium下拉滚动条
    bro.execute_script("arguments[0].scrollIntoView(true);", btn)
    btn.click()
    # bd = bro.find_element_by_xpath('/html/body/div[2]/div[1]/div[1]/ul/li[11]/span')
    # actions = ActionChains(bro)
    # actions.move_to_element(bd).click().perform()

    bro.find_element_by_xpath('/html/body/div[2]/div[1]/div[1]/ul/li[11]')
    # 点击确定按钮
    time.sleep(1)
    bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div/div[3]/button/span').click()
    # 点击统计分析
    bro.find_element_by_xpath('//*[@id="app"]/div/div[1]/div[2]/div[1]/div/ul/div/li[2]/div/span').click()
    # 点击市州设备情况
    bro.find_element_by_xpath('//*[@id="app"]/div/div[1]/div[2]/div[1]/div/ul/div/li[2]/ul/li[3]/span').click()
    time.sleep(3)
    for i in [1,2,3,4,5,6,7,8]:
        # 点击"查看详情"
        fm = bro.find_element_by_xpath('//table/tbody/tr[{0}]/td[7]/div/a'.format(i))
        # '//*[@id="app"]/div/div[2]/div[1]/div/div/div/div/div/div/div[4]/div[1]/div[3]/table/tbody/tr[1]/td[7]/div/a/span'
        bro.execute_script("arguments[0].scrollIntoView();", fm)
        fm.click()
        print(111)
        #点击"离线"
        time.sleep(1)
        bro.find_element_by_xpath('//*[@id="tab-false"]').click()
        #点击"导出"按钮
        export = bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div[1]/div/div[1]/div/div/div/div/div/div[4]/button')
        bro.execute_script("arguments[0].scrollIntoView();", export)
        time.sleep(1.5)
        export.click()
        # time.sleep(4)
        # 点击"返回"
        back = bro.find_element_by_xpath('//*[@id="app"]/div/div[2]/div[1]/div/div/div/div/div/div/div/div[1]/div[1]')
        # bro.execute_script("arguments[0].scrollIntoView();", back)
        back.click()
        # time.sleep(4)
    time.sleep(3)
    bro.quit()

import pandas as pd,numpy as np,datetime,re

# df['是否优秀']=np.where((df.设备id==33722),'优','~')
writer = pd.ExcelWriter('1170_%s.xlsx'% datetime.datetime.now().__format__('%m-%d'))

def format_col_width(ws):
    ws.column_dimensions['A'].width = 22.5
    ws.column_dimensions['B'].width = 37.5
    ws.column_dimensions['D'].width = 22.5
    ws.column_dimensions['E'].width = 22.5
    ws.column_dimensions['E']
    ws.cell(1,5).value = '备注'
    ws.cell(1, 5)

def offline1170(excel_name,proj_name):
    df = pd.read_excel('C:/Users/0717/Downloads/%s.xlsx' % excel_name).drop(['设备id','描述'], axis=1)
    df = df[~ df['连接状态'].str.contains('在线')]
    df.reset_index(drop=True,inplace=True)
    x = []
    index_list = []
    for t, s in df.iterrows():
        temp_str = s[0][0:12]
        if(temp_str in x):
            # print(t,temp_str)
            index_list.append(t)
            # df.drop(df.index[t],inplace=True)
        else:
            x.append(temp_str)
    df.drop(df.index[index_list], inplace=True)
    df.sort_values(by='最后上线时间', ascending=False,inplace=True)
    print(df)
    df.to_excel(writer, sheet_name=proj_name, index=False)
    worksheet = writer.sheets[proj_name]
    format_col_width(worksheet)
    writer.save()

county_dict = {'设备表':'益阳','设备表 (1)':'湘西','设备表 (2)':'长沙','设备表 (3)':'邵阳',
               '设备表 (4)':'张家界',
               '设备表 (5)':'株洲','设备表 (6)':'常德','设备表 (7)':'娄底'}

def export():
    print('f1')
    foo()
    for k,v in county_dict.items():
        offline1170(k, v)

if __name__ == '__main__':
    from line_profiler import LineProfiler
    profile = LineProfiler(export)
    profile.runcall(export)
    profile.print_stats()